/*
Navicat SQL Server Data Transfer

Source Server         : LOCALHOST
Source Server Version : 110000
Source Host           : .:1433
Source Database       : AAACHAM2
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 110000
File Encoding         : 65001

Date: 2014-04-22 22:26:20
*/


-- ----------------------------
-- Table structure for tb0040
-- ----------------------------
DROP TABLE [dbo].[tb0040]
GO
CREATE TABLE [dbo].[tb0040] (
[ID] int IDENTITY(1,1) NOT NULL,
[EmployeeID] varchar(20) NOT NULL ,
[FullName] nvarchar(100) NULL ,
[DepartmentID] varchar(100) NULL ,
[DivisionID] varchar(20) NOT NULL ,
[EmployeeTypeID] varchar(20) NULL ,
[HireDate] datetime NULL ,
[EndDate] datetime NULL ,
[BirthDay] datetime NULL ,
[Address] varchar(250) NULL ,
[Tel] varchar(50) NULL ,
[Fax] varchar(50) NULL ,
[Email] varchar(100) NULL ,
[IsUserID] tinyint NOT NULL DEFAULT ((0)) ,
[Disabled] tinyint NOT NULL DEFAULT ((0)) ,
[CreateDate] datetime NULL ,
[CreateUserID] varchar(50) NULL ,
[LastModifyUserID] varchar(50) NULL ,
[LastModifyDate] datetime NULL 
)


GO

-- ----------------------------
-- Indexes structure for table tb0040
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table tb0040
-- ----------------------------
ALTER TABLE [dbo].[tb0040] ADD PRIMARY KEY NONCLUSTERED ([EmployeeID], [DivisionID])
GO

-- ----------------------------
-- Triggers structure for table tb0040
-- ----------------------------
--DROP TRIGGER [dbo].[AX1103]
GO
CREATE TRIGGER [dbo].[AX1103]
ON [dbo].[tb0040]
AFTER DELETE
AS

If Exists (Select 1 From Deleted Where IsUserID<>0)
	If Exists (Select 1  From tb0074 inner join Deleted Del on Del.EmployeeID = tb0074.UserID)
		Delete  tb0074
		From tb0074 inner join Deleted Del on Del.EmployeeID = tb0074.UserID






GO
--DROP TRIGGER [dbo].[AY1103]
GO
CREATE TRIGGER [dbo].[AY1103]
ON [dbo].[tb0040]
AFTER INSERT
AS
If Exists (Select 1 From Inserted Where IsUserID<>0)
	If not Exists (Select 1  From tb0074 inner join Inserted Ins on Ins.EmployeeID = tb0074.UserID)
			 
			Insert into tb0074(UserID, UserName ,[Password],CreateDate,CreateUserID,LastModifyDate,LastModifyUserID)
			Select EmployeeID, FullName, '', CreateDate, CreateUserID,LastModifyDate,LastModifyUserID
			From inserted


GO
--DROP TRIGGER [dbo].[AZ1103]
GO
CREATE TRIGGER [dbo].[AZ1103]
ON [dbo].[tb0040]
AFTER UPDATE
AS

If Exists (Select 1 From Inserted Where IsUserID<>0)
	If not Exists (Select 1  From tb0074 inner join Inserted Ins on Ins.EmployeeID = tb0074.UserID)
			 
			Insert into tb0074(UserID, UserName ,[Password], CreateDate, CreateUserID,LastModifyDate,LastModifyUserID)
			Select EmployeeID, FullName, '', CreateDate, CreateUserID,LastModifyDate,LastModifyUserID
			From inserted
	Else
		Update tb0074 set UserName = isnull(Ins.FullName,'')
		From tb0074 inner join Inserted Ins on Ins.EmployeeID = tb0074.UserID




GO

-- ----------------------------
-- Records of tb0040
-- ----------------------------
INSERT INTO [dbo].[tb0040] ([EmployeeID], [FullName], [DepartmentID], [DivisionID], [EmployeeTypeID], [HireDate], [EndDate], [BirthDay], [Address], [Tel], [Fax], [Email], [IsUserID], [Disabled], [CreateDate], [CreateUserID], [LastModifyUserID], [LastModifyDate]) VALUES (N'NGAN', N'NGAN', N'', N'HPCNB2013', N'', N'2012-12-28 00:00:00.000', N'2012-12-28 00:00:00.000', N'2012-12-28 00:00:00.000', N'', N'', N'', N'', N'1', N'0', N'2012-12-28 16:14:22.550', N'admin', N'admin', N'2012-12-28 16:14:22.550')
GO
GO
INSERT INTO [dbo].[tb0040] ([EmployeeID], [FullName], [DepartmentID], [DivisionID], [EmployeeTypeID], [HireDate], [EndDate], [BirthDay], [Address], [Tel], [Fax], [Email], [IsUserID], [Disabled], [CreateDate], [CreateUserID], [LastModifyUserID], [LastModifyDate]) VALUES (N'TIEN', N'TIEN', N'', N'HPCNB2013', N'', N'2012-12-28 00:00:00.000', N'2012-12-28 00:00:00.000', N'2012-12-28 00:00:00.000', N'', N'', N'', N'', N'1', N'0', N'2012-12-28 16:14:34.277', N'admin', N'admin', N'2012-12-28 16:14:34.277')
GO
GO
INSERT INTO [dbo].[tb0040] ([EmployeeID], [FullName], [DepartmentID], [DivisionID], [EmployeeTypeID], [HireDate], [EndDate], [BirthDay], [Address], [Tel], [Fax], [Email], [IsUserID], [Disabled], [CreateDate], [CreateUserID], [LastModifyUserID], [LastModifyDate]) VALUES (N'NVNHOM3', N'NVNHOM3', N'', N'HPCNB2013', N'', N'2013-09-03 00:00:00.000', N'2013-09-03 00:00:00.000', N'2013-09-03 00:00:00.000', N'', N'', N'', N'', N'1', N'0', N'2013-09-03 10:54:06.830', N'ADMIN', N'ADMIN', N'2014-04-16 00:54:55.107')
GO
GO
INSERT INTO [dbo].[tb0040] ([EmployeeID], [FullName], [DepartmentID], [DivisionID], [EmployeeTypeID], [HireDate], [EndDate], [BirthDay], [Address], [Tel], [Fax], [Email], [IsUserID], [Disabled], [CreateDate], [CreateUserID], [LastModifyUserID], [LastModifyDate]) VALUES (N'NVNHOM2', N'NVNHOM2', N'', N'HPCNB2013', N'', N'2013-09-03 00:00:00.000', N'2013-09-03 00:00:00.000', N'2013-09-03 00:00:00.000', N'', N'', N'', N'', N'1', N'0', N'2013-09-03 10:54:19.217', N'ADMIN', N'ADMIN', N'2013-09-03 10:54:19.217')
GO
GO
INSERT INTO [dbo].[tb0040] ([EmployeeID], [FullName], [DepartmentID], [DivisionID], [EmployeeTypeID], [HireDate], [EndDate], [BirthDay], [Address], [Tel], [Fax], [Email], [IsUserID], [Disabled], [CreateDate], [CreateUserID], [LastModifyUserID], [LastModifyDate]) VALUES (N'NVNHOM1', N'NVNHOM1', N'', N'HPCNB2013', N'', N'2013-09-03 00:00:00.000', N'2013-09-03 00:00:00.000', N'2013-09-03 00:00:00.000', N'', N'', N'', N'', N'1', N'0', N'2013-09-03 10:54:28.977', N'ADMIN', N'ADMIN', N'2013-09-03 10:54:28.977')
GO
GO
INSERT INTO [dbo].[tb0040] ([EmployeeID], [FullName], [DepartmentID], [DivisionID], [EmployeeTypeID], [HireDate], [EndDate], [BirthDay], [Address], [Tel], [Fax], [Email], [IsUserID], [Disabled], [CreateDate], [CreateUserID], [LastModifyUserID], [LastModifyDate]) VALUES (N'HAD', N'HADDD', N'KD', N'HPCNB2013', N'KT', N'2014-04-16 00:28:26.000', N'2014-05-24 00:28:26.000', N'2014-04-30 00:28:26.000', N'dsfsdf', N'2313123', N'234234', N'dfsdf@yahoo.com', N'1', N'0', N'2014-04-14 17:23:30.370', N'ADMIN', N'ADMIN', N'2014-04-16 00:57:56.240')
GO
GO